IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'spAttachmentInsert') AND type in (N'P', N'PC'))
	DROP PROCEDURE spAttachmentInsert
GO
-- =============================================
-- Author:		Nam Tran
-- Create date: Sep 23, 2014
-- Description:	Insert attachment
-- =============================================
CREATE PROCEDURE [dbo].[spAttachmentInsert]
	@MediaBinary VARBINARY(MAX),
	@MimeType VARCHAR(50),	
	@PressreleaseId BIGINT,
	@FileName NVARCHAR(250),
	@Location VARCHAR(250),
	@DisplayOrder INT,
	@LangId INT,
	@IsBlob BIT
AS
BEGIN
	DECLARE @AttachmentId BIGINT = -1
	-- Have to check for the existance of a Attachment before
	IF NOT EXISTS (SELECT Id FROM Attachment WHERE PressreleaseId = @PressreleaseId AND FileName = @FileName AND LangId = @LangId)
	BEGIN
		INSERT INTO Attachment
		VALUES(
				@MediaBinary,
				@MimeType,
				1,
				@PressreleaseId,
				@FileName,
				@Location,
				@DisplayOrder,
				@LangId,
				@IsBlob,
				GETDATE(),
				GETDATE()
			)
		
		SET @AttachmentId = SCOPE_IDENTITY()
		-- Update Pressreleases table again if insert Attachment successfully
		IF @AttachmentId > 0
		BEGIN
			UPDATE Pressreleases SET Attachment = 1 WHERE Id = @PressreleaseId
		END
	END
	
	SELECT @AttachmentId
END
